import pymysql
import logging
import sys
import time
import os
import cx_Oracle
import psycopg2

# 关闭__pycache__生成
sys.dont_write_bytecode = True
# 添加项目根目录到Python路径
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from logger import setup_logging

# 初始化日志
setup_logging()


db_logger = logging.getLogger('database')

class DatabaseManager:
  def __init__(self, config): 
    """
    初始化数据库管理器
    :param config: 数据库配置字典
    """
    self.config = config
    db_logger.info("加载数据库配置")
    # 连接池或连接缓存
    self.connections = {}

  def get_mysql_connection(self):
    """
    获取MySQL数据库连接
    :return: 数据库连接对象
    """
    try:
      mysql_config = self.config.get('mysql')
      if not mysql_config:
        raise ValueError("MySQL配置信息不存在")
      connection = pymysql.connect(
        host=mysql_config['host'],
        port=mysql_config['port'],
        user=mysql_config['username'],
        password=mysql_config['password'],
        database=mysql_config['database'],
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
      )
      db_logger.info(f"✅ 成功连接到MySQL数据库: {mysql_config['host']}:{mysql_config['port']}/{mysql_config['database']}")
      self.connections['mysql'] = connection
      return connection
    except Exception as e:
      db_logger.error(f"❌ MySQL数据库连接失败: {str(e)}")
      raise e
  
  def get_oracle_connection(self):
    """
    获取Oracle数据库连接
    :return: 数据库连接对象
    """
    try:
      oracle_config = self.config.get('oracle')
      if not oracle_config:
        raise ValueError("Oracle配置信息不存在")
      dsn = f"{oracle_config.get('host')}:{oracle_config.get('port')}/{oracle_config.get('service')}"
      db_logger.info(f"Oracle数据库连接开始: {dsn}")
      connection = cx_Oracle.connect(oracle_config.get("username"), oracle_config.get("password"), dsn)
      db_logger.info(f"✅ Oracle数据库连接成功: {dsn}")
      self.connections['oracle'] = connection
      return connection
    except Exception as e:
      db_logger.error(f"Oracle数据库连接失败: {str(e)}")
      raise

  def get_polardb_connection(self): 
    """
    获取PolarDB数据库连接
    :return: 数据库连接对象
    """
    try:
      polardb_config = self.config.get('polardb')
      if not polardb_config:
          raise ValueError("PolarDB配置信息不存在")
      
      # PolarDB兼容Oracle协议
      connection = psycopg2.connect(
        host=polardb_config['host'],         # PolarDB 连接地址
        port=polardb_config['port'],         # 默认通常为 5432
        database=polardb_config['database'], # 数据库名
        user=polardb_config['username'],         # 用户名
        password=polardb_config['password']  # 密码
      )

      db_logger.info(f"✅ 成功连接到PolarDB数据库: {polardb_config['host']}:{polardb_config['port']}/{polardb_config['database']}")
      self.connections['polardb'] = connection
      return connection
    except Exception as e:
      db_logger.error(f"❌ PolarDB数据库连接失败: {str(e)}")
      raise

  def close_connection(self, connection):
    """关闭数据库连接"""
    if connection:
      try:
        connection.close()
        db_logger.info("数据库连接已关闭")
      except Exception as e:
        db_logger.error(f"关闭数据库连接失败: {str(e)}")
  
  def test_connection(self, db_type='mysql'): 
    """
    测试数据库连接
    :param db_type: 数据库类型，支持 'oracle', 'mysql', 'polardb'
    :return: 连接是否成功
    """
    try:
      print(f"\n===== 测试 {db_type.upper()} 数据库连接 ====")
      start_time = time.time()
      if (db_type.lower() == 'oracle'):
        connection = self.get_oracle_connection()
      elif (db_type.lower() == 'mysql'):  
        connection = self.get_mysql_connection()
      elif (db_type.lower() == 'polardb'):
        connection = self.get_polardb_connection()
      else:
        raise ValueError(f"不支持的数据库类型: {db_type}")

      end_time = time.time()
      # 关闭连接
      self.close_connection(connection)
      
      print(f"✅ {db_type.upper()} 数据库连接成功!")
      print(f"⏱️ 连接耗时: {end_time - start_time:.4f} 秒")
      return True
    except Exception as e:
      print(f"❌ {db_type.upper()} 数据库连接失败: {str(e)}")
      return False

  def execute_sql_mysql(self, sql, params=None):
    try:
      # 获取数据库连接
      connection = self.get_mysql_connection()
      sql_to_execute = sql or "SELECT NOW() as `current_time`, DATABASE() as current_db"
      with connection.cursor() as cursor:
        cursor.execute(sql_to_execute, params)
        if sql_to_execute.strip().upper().startswith('SELECT'):
          results = cursor.fetchall()
          print("SQL执行成功!")
          print(f"📊 查询结果 ({len(results)} 行):")
          return results
        else:
          # 非查询语句，提交事务
          connection.commit()
          print(f"✅ SQL执行成功! 影响行数: {cursor.rowcount}")
          return cursor.rowcount
    except Exception as e:
      db_logger.error(f"执行SQL失败: {str(e)}")
      if 'connection' in locals():
        self.close_connection(connection)
      return None
    finally:
      if 'connection' in locals():
        self.close_connection(connection)
  
  def execute_sql_oracle(self, sql=None, params=None):
    try:
      # 获取数据库连接
      connection = self.get_oracle_connection()
      sql_to_execute = sql or "SELECT SYSDATE FROM DUAL"
      with connection.cursor() as cursor:
        cursor.execute(sql_to_execute, params)
        if sql_to_execute.strip().upper().startswith('SELECT'):
          results = cursor.fetchall()
          print("✅ SQL执行成功!")
          return results
        else:
          # 非查询语句，提交事务
          connection.commit()
          print(f"✅ SQL执行成功! 影响行数: {cursor.rowcount}")
          return cursor.rowcount
    except cx_Oracle.DatabaseError as e:
      db_logger.error(f"执行SQL失败: {str(e)}")
      if 'connection' in locals():
        self.close_connection(connection)
      return None
    finally:
      # 关闭游标和连接
      if cursor:
          cursor.close()
      if 'connection' in locals():
        self.close_connection(connection)

  def execute_sqls_commit(self, sqls, db_type='mysql', params=None):
    try:
      # 获取数据库连接
      if db_type == 'oracle':
        connection = self.get_oracle_connection()
      elif db_type == 'polardb':
        connection = self.get_polardb_connection()
      else:
        connection = self.get_mysql_connection()
      with connection.cursor() as cursor:
        for sql in sqls:
          print(sql)
          cursor.execute(sql, params)
        # 非查询语句，提交事务
        connection.commit()
        print(f"✅ SQL执行成功!，SQL执行条数：{len(sqls)}条")
    except Exception as e:
      db_logger.error(f"❌ 执行SQL失败: {str(e)}")
    finally:
      if 'connection' in locals():
        self.close_connection(connection)